const insert_visitor = "insert into smart_visitor(user_id,visitor_house,visitor_name,visitor_tel,visitor_mine_status,visitor_house_status,visitor_status,visitor_date) values (?,?,?,?,1,0,0,?)";

const select_visitorByuser = "select smart_visitor.id,smart_visitor.user_id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id where user_id = ?"

const select_visitor = "select smart_visitor.id,smart_visitor.user_id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id where visitor_mine_status=1 and smart_visitor.id = ?"

const select_myHouseVisitor = "select smart_visitor.id,smart_visitor.user_id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where smart_household_owner.user_id = ?";

const revoke_visitor = "update smart_visitor set visitor_mine_status=0 where user_id=? and id=?";

const select_houseOwn = "select smart_household_owner.user_id from smart_visitor left join smart_house on smart_visitor.visitor_house = smart_house.id left join smart_household_owner on smart_house.owner_id = smart_household_owner.id where smart_visitor.id=?";

const change_visitor_houseOwner = "update smart_visitor set visitor_house_status=? where id=?"

//管理端
//获取访客列表(默认降序)
const select_visitor_admin = "select smart_visitor.id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id order by smart_visitor.visitor_date desc limit ? offset ?";
//获取访客列表(时间升序)
const select_visitorByAsc_admin = "select smart_visitor.id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id order by smart_visitor.visitor_date asc limit ? offset ?";
//模糊搜索访客名称
const select_visitor_admin_keyWord = "select smart_visitor.id,smart_building.building_name,smart_house.house_plate,smart_visitor.visitor_name,smart_visitor.visitor_tel,smart_visitor.visitor_mine_status,smart_visitor.visitor_house_status,smart_visitor.visitor_status,smart_visitor.visitor_date from smart_visitor left join smart_house on smart_visitor.visitor_house=smart_house.id left join smart_building on smart_house.building_id=smart_building.id where smart_visitor.visitor_name like concat('%',?,'%')";
//修改访客审核状态
const change_visitor_admin = "update smart_visitor set visitor_status=? where id=?";
//获取申请者的openId
const select_visitorID_admin = "select user_id,openid from smart_visitor left join smart_user on smart_visitor.user_id = smart_user.id where smart_visitor.id=?";
//删除访客申请
const delete_visitor_admin = "delete from smart_visitor where id = ?";


module.exports = {
    insert_visitor,
    select_visitorByuser,
    select_visitor,
    revoke_visitor,
    select_houseOwn,
    change_visitor_houseOwner,
    select_myHouseVisitor,
    select_visitor_admin,
    select_visitorByAsc_admin,
    select_visitor_admin_keyWord,
    change_visitor_admin,
    select_visitorID_admin,
    delete_visitor_admin
}